from random import randint

from sqlalchemy import Column, Integer, String, ForeignKey
from sqlalchemy.orm import relationship, backref

from db_util import Base, Session,engine


class User(Base):
    __tablename__ = 't_user'
    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(32))
    age = Column(Integer)

    def __repr__(self):
        return f'<User: id={self.id} name={self.name} age={self.age}>'


class News(Base):
    __tablename__ = 't_news'
    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(32), nullable=False)
    content = Column(String(32), nullable=False)
    read_count = Column(Integer)

    uid = Column(Integer, ForeignKey('t_user.id'))
    user = relationship('User', backref=backref('newss',order_by=read_count))

    def __repr__(self):
        return f'<User: id={self.id} title={self.title} content={self.content} read_count={self.read_count}>'


def create_user():
    with Session() as ses:
        for i in range(10):
            user = User(name=f'用户{i}', age=randint(6, 20))
            ses.add(user)

        for i in range(10):
            news = News(title=f'新闻{i}', content='新闻', read_count=randint(1, 1000))
            user.newss.append(news)
        ses.commit()


def query_user():
    with Session() as ses:
        # users = ses.query(User).order_by(User.age).all()  #升序
        users = ses.query(User).order_by(User.age.desc()).all()    #降序
        for i in users:
            print(i)

def query_news():
    with Session() as ses:
        users = ses.query(User).all()
        for i in users[-1].newss:
            print(i)


if __name__ == '__main__':
    # Base.metadata.drop_all(engine)
    # Base.metadata.create_all(engine)
    # create_user()
    # query_user()
    query_news()

